﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TravelSite.Entities;
using Dapper;

namespace TravelSite.DAL.Repositories
{
    public class ContactRepository : BaseRepository<Contact>
    {
        protected override string TableName
        {
            get { return "[Contact]"; }
        }

        public IEnumerable<Contact> Search(int? agentId, string letter)
        {
            return Search(agentId, letter, "", "");
        }

        public IEnumerable<Contact> Search(int? agentId, string letter, string columnName, string columnValue)
        {
            if (string.IsNullOrEmpty(columnValue))
                columnName = "";

            return Connection.Query<Contact>("spContact_Search @AgentId, @Letter, @ColumnName, @ColumnValue", 
                new { AgentId = (agentId == -1 ? null : agentId), 
                        Letter = (letter == "ALL" ? null : letter),
                      ColumnName = (string.IsNullOrEmpty(columnName) ? null : columnName),
                      ColumnValue = (string.IsNullOrEmpty(columnValue) ? null : columnValue)
                });
        }

        public override Contact Find(int contactId)
        {
            return Connection.Query<Contact>(
                @"DECLARE @packId int;
                SET @packId = (SELECT TOP(1) PackageId FROM [dbo].[ClientPackage] WHERE ClientId = @ContactId AND Status='BOOKED' ORDER BY PackageId DESC);  
                SELECT c.ContactId, c.AgentId, c.FirstName, c.LastName, c.Address1, c.Address2, 
                            c.City, c.CountryId, c.StateId, c.Zip, c.Email, c.MPhone, c.WPhone, c.HPhone, c.CompanyName, c.JobTitle, 
                            c.PartnerFirstName, c.PartnerLastName, c.DateOfBirth, c.Notes,
                            [Country].CountryName, s.StateCode, u.FirstName + ' ' + u.LastName as AgentName,
                            c.Pin,

                            c.SalesFormDate, c.SalesFormList, c.SalesFormEmailed, c.SalesFormOpener, c.SalesFormCloser, 
                            c.SalesFormManagerPrice, c.SalesFormProcessingFee, c.SalesFormTotal, 
                            c.SalesFormMarriedType, c.SalesFormCoHabMatchingIds, c.SalesFormSingleGender, c.SalesFormSingleOwnTimeshare, c.SalesFormSingleOwnTimeshareName, 
                            c.SalesFormIncome, c.SalesFormAge, 

                            c.SalesFormGuestFirstName, c.SalesFormGuestLastName, c.SalesFormGuestDOB, 
                            c.SalesFormSpouseDOB, c.SalesFormBillingAddress, 
                            c.SalesFormMajorCreditCard, c.SalesFormNameOnCard, c.SalesFormCardExp, c.SalesFormCardNo, c.SalesFormCardCVV, c.SalesFormCardType, 
                            c.SalesFormDestinationLasVegas, c.SalesFormDestinationOpen, 
                            c.SalesFormDestination4days3nights, c.SalesFormDestination3days2nights, 
                            c.SalesFormGiftsDinner, c.SalesFormGifts100, c.SalesFormGiftsCompTix, 
                            c.SalesFormBonusVacationLand, c.SalesFormBonusVacationCondo, c.SalesFormBonusVacationBahamas, 
                            c.SalesFormOwnTimeshare, c.SalesFormOwnTimeshareName, c.SalesFormOwnTimeshareTotalTix, c.SalesFormOwnTimeshareNotes, 
                            c.SalesFormVerifiedBy, c.SalesFormMerchant, c.SalesFormConfirmation, c.SalesFormCCAuth, c.SalesFormTime,
                            c.SalesFormBonus1, c.SalesFormBonus2, c.SalesFormBonus3,

                            c.Gift1, c.Gift2, c.Gift3, c.Gift4, c.Gift5, c.Gift6, c.Gift7, c.Gift8,
                            c.BankInfoDate, c.BankInfoAmount, c.BankInfoPaymentId, c.VacationCompanyId,
                            c.TourDate, c.TourTime, c.TourHotelId, c.TourCompanyId, c.TourHotelCost, c.TourNotes, c.Verified, c.VerificationsSend, c.ConfirmationNumber,  c.UpgradeFee, c.Gift, c.GiftCost, c.Comments,
                            c.WholesalerId, c.HotelbedsInvoiceNumber, c.HotelbedsResNumber, 
                            c.HotelbedsBookedDate, c.HotelbedsArrivalDate, c.HotelbedsDepartureDate, c.HotelbedsPaymentDate, 
                            c.HotelbedsClient, c.HotelbedsTotalWholesaleCost, c.HotelbedsExpoPaidDate, c.HotelbedsTotalPaid, 
                            c.HotelbedsCheckNumber, c.HotelbedsHBPaidDate,
                            c.EmailSettingId, p.[Cost] AS PackagePrice

                    FROM [Contact] (nolock) c left join [State] (nolock) s on c.StateId = s.StateId
                                                left join [User] (nolock) u on c.AgentId = u.Id
                                                left join [Country] (nolock) on c.CountryId = [Country].CountryId
                                                left join [TravelPackage] (nolock) p on p.PackageId = @packId
                    WHERE c.ContactId = @ContactId", new { ContactId = contactId }).SingleOrDefault();
        }

        public IEnumerable<Contact> BankInfoList(DateTime? dateFrom, DateTime? dateTo)
        {
            IEnumerable<Contact> result = Connection.Query<Contact>(
                @"select c.ContactId, c.FirstName, c.LastName, c.BankInfoDate, c.BankInfoAmount, c.BankInfoPaymentId, c.VacationCompanyId,
                        p.PaymentName, vc.VacationCompanyName 
                    from [Contact] (nolock) c left join [Payment] p on c.BankInfoPaymentId = p.PaymentId
                                            left join [VacationCompanies] vc on c.VacationCompanyId = vc.VacationCompanyId  
                    order by c.FirstName, c.LastName
                ");

            if (dateFrom.HasValue)
                result = result.Where(x => x.BankInfoDate >= dateFrom.Value);
            if (dateTo.HasValue)
                result = result.Where(x => x.BankInfoDate <= dateTo.Value);

            return result;
        }

        public IEnumerable<Contact> TourList(DateTime? dateFrom, DateTime? dateTo)
        {
            IEnumerable<Contact> result = Connection.Query<Contact>(
                @"select c.ContactId, c.FirstName, c.LastName, 
                        c.TourDate, c.TourTime, c.TourHotelId, c.TourCompanyId, c.TourHotelCost, c.TourNotes, 
                        c.Verified, c.VerificationsSend, c.ConfirmationNumber,  c.UpgradeFee, c.Gift, c.GiftCost, c.Comments,
                        t.TourName, tc.TourCompanyName 
                    from [Contact] (nolock) c left join [Tour] t on c.TourHotelId = t.TourId
                                            left join [TourCompanies] tc on c.TourCompanyId = tc.TourCompanyId  
                    order by c.FirstName, c.LastName
                ");

            if (dateFrom.HasValue)
                result = result.Where(x => x.TourDateDate >= dateFrom.Value);
            if (dateTo.HasValue)
                result = result.Where(x => x.TourDateDate <= dateTo.Value);

            return result;
        }

        public IEnumerable<Contact> BookingList(string dateColumn, DateTime? dateFrom, DateTime? dateTo)
        {
            IEnumerable<Contact> result = Connection.Query<Contact>(
                @"select c.ContactId, c.FirstName, c.LastName, 
                        c.WholesalerId, c.HotelbedsInvoiceNumber, c.HotelbedsResNumber, 
                        c.HotelbedsBookedDate, c.HotelbedsArrivalDate, c.HotelbedsDepartureDate, c.HotelbedsPaymentDate, 
                        c.HotelbedsClient, c.HotelbedsTotalWholesaleCost, c.HotelbedsExpoPaidDate, c.HotelbedsTotalPaid, 
                        c.HotelbedsCheckNumber, c.HotelbedsHBPaidDate,
                        c.VacationCompanyId, vc.VacationCompanyName, w.WholesalerName
                    from [Contact] (nolock) c left join [Wholesalers] w on c.WholesalerId = w.WholesalerId
                                            left join [VacationCompanies] vc on c.VacationCompanyId = vc.VacationCompanyId  
                    order by c.FirstName, c.LastName
                ");

            if (dateColumn == "HotelbedsBookedDate")
            {
                if (dateFrom.HasValue)
                    result = result.Where(x => x.HotelbedsBookedDate >= dateFrom.Value);
                if (dateTo.HasValue)
                    result = result.Where(x => x.HotelbedsBookedDate <= dateTo.Value);
            }
            else if (dateColumn == "HotelbedsArrivalDate")
            {
                if (dateFrom.HasValue)
                    result = result.Where(x => x.HotelbedsArrivalDate >= dateFrom.Value);
                if (dateTo.HasValue)
                    result = result.Where(x => x.HotelbedsArrivalDate <= dateTo.Value);
            }
            else if (dateColumn == "HotelbedsDepartureDate")
            {
                if (dateFrom.HasValue)
                    result = result.Where(x => x.HotelbedsDepartureDate >= dateFrom.Value);
                if (dateTo.HasValue)
                    result = result.Where(x => x.HotelbedsDepartureDate <= dateTo.Value);
            }
            else if (dateColumn == "HotelbedsPaymentDate")
            {
                if (dateFrom.HasValue)
                    result = result.Where(x => x.HotelbedsPaymentDate >= dateFrom.Value);
                if (dateTo.HasValue)
                    result = result.Where(x => x.HotelbedsPaymentDate <= dateTo.Value);
            }
            else if (dateColumn == "HotelbedsExpoPaidDate")
            {
                if (dateFrom.HasValue)
                    result = result.Where(x => x.HotelbedsExpoPaidDate >= dateFrom.Value);
                if (dateTo.HasValue)
                    result = result.Where(x => x.HotelbedsExpoPaidDate <= dateTo.Value);
            }
            else if (dateColumn == "HotelbedsHBPaidDate")
            {
                if (dateFrom.HasValue)
                    result = result.Where(x => x.HotelbedsHBPaidDate >= dateFrom.Value);
                if (dateTo.HasValue)
                    result = result.Where(x => x.HotelbedsHBPaidDate <= dateTo.Value);
            }

            return result;
        }

        public Contact GetLastUpdatedSalesForm()
        {
            return Connection.Query<Contact>(
                @"  SELECT top(1) c.Gift1, c.Gift2, c.Gift3, c.Gift4, c.Gift5, c.Gift6, c.Gift7, c.Gift8
                    FROM [Contact] (nolock) c
                    WHERE SalesFormUpdatedAt is not null
                    order by SalesFormUpdatedAt desc").SingleOrDefault();
        }

        public bool CheckPin(int contactId, string pin)
        {
            return Connection.Query<bool>("select convert(bit, case when exists(select 1 from [Contact] (nolock) where Pin = @Pin and ContactId <> @ContactId) then 0 else 1 end) as Result",
                new { ContactId = contactId, Pin = pin }).SingleOrDefault();
        }

        public override void Insert(Contact contact)
        {
            Connection.Execute(
                @"INSERT INTO [Contact] 
                (AgentId, FirstName, LastName, Address1, Address2, City, CountryId, StateId, Zip, 
                    Email, MPhone, WPhone, HPhone, CompanyName, JobTitle,
                    PartnerFirstName, PartnerLastName, DateOfBirth, Notes, Pin,
                    BankInfoDate, BankInfoAmount, BankInfoPaymentId, VacationCompanyId,
                    TourDate, TourTime, TourHotelId, TourCompanyId, TourHotelCost, TourNotes, Verified, VerificationsSend, ConfirmationNumber, UpgradeFee, Gift, GiftCost, Comments,
                    WholesalerId, HotelbedsInvoiceNumber, HotelbedsResNumber, 
                    HotelbedsBookedDate, HotelbedsArrivalDate, HotelbedsDepartureDate, HotelbedsPaymentDate, 
                    HotelbedsClient, HotelbedsTotalWholesaleCost, HotelbedsExpoPaidDate, HotelbedsTotalPaid, 
                    HotelbedsCheckNumber, HotelbedsHBPaidDate, EmailSettingId) 
                VALUES (@AgentId, @FirstName, @LastName, @Address1, @Address2, @City, @CountryId, @StateId, @Zip, 
                    @Email, @MPhone, @WPhone, @HPhone, @CompanyName, @JobTitle,
                    @PartnerFirstName, @PartnerLastName, @DateOfBirth, @Notes, @Pin,
                    @BankInfoDate, @BankInfoAmount, @BankInfoPaymentId, @VacationCompanyId,
                    @TourDate, @TourTime, @TourHotelId, @TourCompanyId, @TourHotelCost, @TourNotes, @Verified, @VerificationsSend, @ConfirmationNumber, @UpgradeFee, @Gift, @GiftCost, @Comments,
                    @WholesalerId, @HotelbedsInvoiceNumber, @HotelbedsResNumber, 
                    @HotelbedsBookedDate, @HotelbedsArrivalDate, @HotelbedsDepartureDate, @HotelbedsPaymentDate, 
                    @HotelbedsClient, @HotelbedsTotalWholesaleCost, @HotelbedsExpoPaidDate, @HotelbedsTotalPaid, 
                    @HotelbedsCheckNumber, @HotelbedsHBPaidDate, @EmailSettingId)", contact);
        }

        public override void Update(Contact contact)
        {
            Connection.Execute(
                @"update [Contact] set AgentId = @AgentId, FirstName = @FirstName, LastName = @LastName, 
                    Address1 = @Address1, Address2 = @Address2, City = @City, CountryId = @CountryId, StateId = @StateId, 
                    Zip = @Zip, Email = @Email, MPhone = @MPhone, WPhone = @WPhone, HPhone = @HPhone, 
                    CompanyName = @CompanyName, JobTitle = @JobTitle,
                    PartnerFirstName = @PartnerFirstName, PartnerLastName = @PartnerLastName, 
                    DateOfBirth = @DateOfBirth, Notes = @Notes, Pin = @Pin,
                    BankInfoDate = @BankInfoDate, BankInfoAmount = @BankInfoAmount, BankInfoPaymentId = @BankInfoPaymentId, VacationCompanyId = @VacationCompanyId,
                    TourDate = @TourDate, TourTime = @TourTime, TourHotelId = @TourHotelId, TourCompanyId = @TourCompanyId, TourHotelCost = @TourHotelCost, TourNotes = @TourNotes, 
                    Verified = @Verified, VerificationsSend = @VerificationsSend, ConfirmationNumber = @ConfirmationNumber,  UpgradeFee = @UpgradeFee, 
                    Gift = @Gift, GiftCost = @GiftCost, Comments = @Comments,
                    WholesalerId = @WholesalerId, HotelbedsInvoiceNumber = @HotelbedsInvoiceNumber, HotelbedsResNumber = @HotelbedsResNumber, 
                    HotelbedsBookedDate = @HotelbedsBookedDate, HotelbedsArrivalDate = @HotelbedsArrivalDate, 
                    HotelbedsDepartureDate = @HotelbedsDepartureDate, HotelbedsPaymentDate = @HotelbedsPaymentDate, 
                    HotelbedsClient = @HotelbedsClient, HotelbedsTotalWholesaleCost = @HotelbedsTotalWholesaleCost, 
                    HotelbedsExpoPaidDate = @HotelbedsExpoPaidDate, HotelbedsTotalPaid = @HotelbedsTotalPaid, 
                    HotelbedsCheckNumber = @HotelbedsCheckNumber, HotelbedsHBPaidDate = @HotelbedsHBPaidDate,
                    EmailSettingId = @EmailSettingId
                where ContactId = @ContactId", contact);
        }

        public void UpdateBankInfo(Contact contact)
        {
            Connection.Execute(
                @"update [Contact] 
                set BankInfoDate = @BankInfoDate, BankInfoAmount = @BankInfoAmount, 
                    BankInfoPaymentId = @BankInfoPaymentId, VacationCompanyId = @VacationCompanyId
                where ContactId = @ContactId", contact);
        }

        public void UpdateTour(Contact contact)
        {
            Connection.Execute(
                @"update [Contact] 
                set TourDate = @TourDate, TourTime = @TourTime, TourHotelId = @TourHotelId, TourCompanyId = @TourCompanyId, TourHotelCost = @TourHotelCost, TourNotes = @TourNotes, 
                    Verified = @Verified, VerificationsSend = @VerificationsSend, ConfirmationNumber = @ConfirmationNumber,  UpgradeFee = @UpgradeFee, 
                    Gift = @Gift, GiftCost = @GiftCost, Comments = @Comments
                where ContactId = @ContactId", contact);
        }

        public void UpdateHotelbeds(Contact contact)
        {
            Connection.Execute(
                @"update [Contact] 
                set WholesalerId = @WholesalerId, HotelbedsInvoiceNumber = @HotelbedsInvoiceNumber, HotelbedsResNumber = @HotelbedsResNumber, 
                    HotelbedsBookedDate = @HotelbedsBookedDate, HotelbedsArrivalDate = @HotelbedsArrivalDate, 
                    HotelbedsDepartureDate = @HotelbedsDepartureDate, HotelbedsPaymentDate = @HotelbedsPaymentDate, 
                    HotelbedsClient = @HotelbedsClient, HotelbedsTotalWholesaleCost = @HotelbedsTotalWholesaleCost, 
                    HotelbedsExpoPaidDate = @HotelbedsExpoPaidDate, HotelbedsTotalPaid = @HotelbedsTotalPaid, 
                    HotelbedsCheckNumber = @HotelbedsCheckNumber, HotelbedsHBPaidDate = @HotelbedsHBPaidDate, VacationCompanyId = @VacationCompanyId
                where ContactId = @ContactId", contact);
        }

        public void UpdateSalesForm(Contact contact)
        {
            Connection.Execute(
                @"update [Contact] set SalesFormDate = @SalesFormDate, SalesFormList = @SalesFormList, SalesFormEmailed = @SalesFormEmailed, 
                    AgentId = @AgentId, SalesFormCloser = @SalesFormCloser, 
                    SalesFormManagerPrice = @SalesFormManagerPrice, SalesFormProcessingFee = @SalesFormProcessingFee, SalesFormTotal = @SalesFormTotal, 
                    SalesFormMarriedType = @SalesFormMarriedType, SalesFormCoHabMatchingIds = @SalesFormCoHabMatchingIds, SalesFormSingleGender = @SalesFormSingleGender, 
                    SalesFormSingleOwnTimeshare = @SalesFormSingleOwnTimeshare, SalesFormSingleOwnTimeshareName = @SalesFormSingleOwnTimeshareName, 
                    SalesFormIncome = @SalesFormIncome, SalesFormAge = @SalesFormAge, 

                    FirstName = @FirstName, LastName = @LastName, DateOfBirth = @DateOfBirth, 
                    PartnerFirstName = @PartnerFirstName, PartnerLastName = @PartnerLastName, SalesFormSpouseDOB = @SalesFormSpouseDOB, 

                    SalesFormBillingAddress = @SalesFormBillingAddress, 
                    CountryId = @CountryId, StateId = @StateId, Zip = @Zip, City = @City, MPhone = @MPhone, WPhone = @WPhone, Email = @Email,
                    SalesFormMajorCreditCard = @SalesFormMajorCreditCard, SalesFormNameOnCard = @SalesFormNameOnCard, SalesFormCardExp = @SalesFormCardExp, 
                    SalesFormCardNo = @SalesFormCardNo, SalesFormCardCVV = @SalesFormCardCVV, SalesFormCardType = @SalesFormCardType, 

                    SalesFormDestinationLasVegas = @SalesFormDestinationLasVegas, SalesFormDestinationOpen = @SalesFormDestinationOpen, 
                    SalesFormDestination4days3nights = @SalesFormDestination4days3nights, SalesFormDestination3days2nights = @SalesFormDestination3days2nights, 
                    SalesFormGiftsDinner = @SalesFormGiftsDinner, SalesFormGifts100 = @SalesFormGifts100, 
                    SalesFormGiftsCompTix = @SalesFormGiftsCompTix, 
                    SalesFormBonusVacationLand = @SalesFormBonusVacationLand, SalesFormBonusVacationCondo = @SalesFormBonusVacationCondo, SalesFormBonusVacationBahamas = @SalesFormBonusVacationBahamas, 
                    SalesFormOwnTimeshare = @SalesFormOwnTimeshare, SalesFormOwnTimeshareName = @SalesFormOwnTimeshareName, 
                    SalesFormOwnTimeshareTotalTix = @SalesFormOwnTimeshareTotalTix, SalesFormOwnTimeshareNotes = @SalesFormOwnTimeshareNotes, 
                    SalesFormVerifiedBy = @SalesFormVerifiedBy, SalesFormMerchant = @SalesFormMerchant, SalesFormConfirmation = @SalesFormConfirmation, 
                    SalesFormCCAuth = @SalesFormCCAuth, SalesFormTime = @SalesFormTime, 
                    SalesFormBonus1 = @SalesFormBonus1, SalesFormBonus2 = @SalesFormBonus2, SalesFormBonus3 = @SalesFormBonus3,

                    Gift1 = @Gift1,
	                Gift2 = @Gift2,
	                Gift3 = @Gift3,
	                Gift4 = @Gift4,
	                Gift5 = @Gift5,
	                Gift6 = @Gift6,
	                Gift7 = @Gift7,
	                Gift8 = @Gift8,

                    SalesFormUpdatedAt = getdate()
                where ContactId = @ContactId", contact);
        }

        public int InsertSalesForm(Contact contact)
        {
            int contactId = Connection.Query<int>(
                @"INSERT INTO [Contact] 
                (AgentId, SalesFormDate, SalesFormList, SalesFormEmailed, 
                    SalesFormOpener, SalesFormCloser, 
                    SalesFormManagerPrice, SalesFormProcessingFee, SalesFormTotal, 
                    SalesFormMarriedType, SalesFormCoHabMatchingIds, SalesFormSingleGender, 
                    SalesFormSingleOwnTimeshare, SalesFormSingleOwnTimeshareName, 
                    SalesFormIncome, SalesFormAge, 

                    FirstName, LastName, DateOfBirth, 
                    PartnerFirstName, PartnerLastName, SalesFormSpouseDOB, 

                    SalesFormBillingAddress, 
                    CountryId, StateId, Zip, City, MPhone, WPhone, Email,
                    SalesFormMajorCreditCard, SalesFormNameOnCard, SalesFormCardExp, 
                    SalesFormCardNo, SalesFormCardCVV, SalesFormCardType, 

                    SalesFormDestinationLasVegas, SalesFormDestinationOpen, 
                    SalesFormDestination4days3nights, SalesFormDestination3days2nights, 
                    SalesFormGiftsDinner, SalesFormGifts100, 
                    SalesFormGiftsCompTix, 
                    SalesFormBonusVacationLand, SalesFormBonusVacationCondo, SalesFormBonusVacationBahamas, 
                    SalesFormOwnTimeshare, SalesFormOwnTimeshareName, 
                    SalesFormOwnTimeshareTotalTix, SalesFormOwnTimeshareNotes, 
                    SalesFormVerifiedBy, SalesFormMerchant, SalesFormConfirmation, 
                    SalesFormCCAuth, SalesFormTime,
                    SalesFormBonus1, SalesFormBonus2, SalesFormBonus3,
                    Gift1,
	                Gift2,
	                Gift3,
	                Gift4,
	                Gift5,
	                Gift6,
	                Gift7,
	                Gift8,

                    SalesFormUpdatedAt
                    ) 
                VALUES (@AgentId, @SalesFormDate, @SalesFormList, @SalesFormEmailed, 
                    @SalesFormOpener, @SalesFormCloser, 
                    @SalesFormManagerPrice, @SalesFormProcessingFee, @SalesFormTotal, 
                    @SalesFormMarriedType, @SalesFormCoHabMatchingIds, @SalesFormSingleGender, 
                    @SalesFormSingleOwnTimeshare, @SalesFormSingleOwnTimeshareName, 
                    @SalesFormIncome, @SalesFormAge, 

                    @FirstName, @LastName, @DateOfBirth, 
                    @PartnerFirstName, @PartnerLastName, @SalesFormSpouseDOB, 

                    @SalesFormBillingAddress, 
                    @CountryId, @StateId, @Zip, @City, @MPhone, @WPhone, @Email,
                    @SalesFormMajorCreditCard, @SalesFormNameOnCard, @SalesFormCardExp, 
                    @SalesFormCardNo, @SalesFormCardCVV, @SalesFormCardType, 

                    @SalesFormDestinationLasVegas, @SalesFormDestinationOpen, 
                    @SalesFormDestination4days3nights, @SalesFormDestination3days2nights, 
                    @SalesFormGiftsDinner, @SalesFormGifts100, 
                    @SalesFormGiftsCompTix, 
                    @SalesFormBonusVacationLand, @SalesFormBonusVacationCondo, @SalesFormBonusVacationBahamas, 
                    @SalesFormOwnTimeshare, @SalesFormOwnTimeshareName, 
                    @SalesFormOwnTimeshareTotalTix, @SalesFormOwnTimeshareNotes, 
                    @SalesFormVerifiedBy, @SalesFormMerchant, @SalesFormConfirmation, 
                    @SalesFormCCAuth, @SalesFormTime,
                    @SalesFormBonus1, @SalesFormBonus2, @SalesFormBonus3,
                    @Gift1,
	                @Gift2,
	                @Gift3,
	                @Gift4,
	                @Gift5,
	                @Gift6,
	                @Gift7,
	                @Gift8,

                    @SalesFormUpdatedAt
                    );
                SELECT CAST(SCOPE_IDENTITY() AS INT);", contact).Single();

            return contactId;
        }

        public override void Delete(int id)
        {
            Connection.Execute(
                @"delete from [Contact]
                    where ContactId = @ContactId", new { ContactId = id });
        }

        public string GetColumnValue(int contactId, string columnName)
        {
            string strSQL = "";
            /*if (columnName != "State")
                strSQL = string.Format("select {0} from Contact (nolock) where ContactId = {1}", columnName, contactId.ToString());
            else
                strSQL = string.Format("select State.StateName from Contact (nolock) left join State (nolock) on Contact.StateId = State.StateId where ContactId = {1}", columnName, contactId.ToString());*/
            strSQL = string.Format("select dbo.fnContactColumnValue({0}, '{1}')", contactId.ToString(), columnName);
            return Connection.Query<string>(strSQL).Single();
        }

        public List<Alphabet> GetAlphabet(bool includeAll, string allPos, string selectedValue)
        {
            List<Alphabet> alphabets = new List<Alphabet>();

            Alphabet alphabetAll = new Alphabet();
            alphabetAll.Value = "ALL";
            alphabetAll.Selected = (alphabetAll.Value == selectedValue);

            if ((includeAll) && (allPos == "first"))
                alphabets.Add(alphabetAll);

            for (int i = 65; i <= 90; i++)
            {
                Alphabet alphabet = new Alphabet();
                alphabet.Value = Char.ConvertFromUtf32(i);
                alphabet.Selected = (alphabet.Value == selectedValue);
                alphabets.Add(alphabet);
            }

            if ((includeAll) && (allPos == "last"))
                alphabets.Add(alphabetAll);

            return alphabets;
        }

        public IEnumerable<Country> CountriesCombo(bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as CountryId, 'ALL' as CountryName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as CountryId, '(Select Country)' as CountryName, 0 as SortIndex union all ";

            strSQL += @"select CountryId, CountryName, 1 as SortIndex from [Country] (nolock) order by SortIndex, CountryName";
            return Connection.Query<Country>(strSQL);
        }

        public IEnumerable<State> StatesCombo(bool includeAll, bool includeEmpty, int countryId)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as StateId, 'ALL' as StateName, 'ALL' AS StateCode, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as StateId, '(Select State)' as StateName, '(Select State)' AS StateCode, 0 as SortIndex union all ";

            strSQL += string.Format(@"select StateId, StateName, StateCode, 1 as SortIndex from [State] (nolock) where CountryId = {0} order by SortIndex, StateCode", countryId.ToString());
            return Connection.Query<State>(strSQL);
        }

        public IEnumerable<Payment> PaymentsCombo(bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as PaymentId, 'ALL' as PaymentName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as PaymentId, '(Payment Method)' as PaymentName, 0 as SortIndex union all ";

            strSQL += @"select PaymentId, PaymentName, 1 as SortIndex from [Payment] (nolock) order by SortIndex, PaymentName";
            return Connection.Query<Payment>(strSQL);
        }

        public IEnumerable<EmailSetting> EmailSettingsCombo(bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as EmailSettingId, 'ALL' as EmailSettingName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as EmailSettingId, '(Select EmailSetting)' as EmailSettingName, 0 as SortIndex union all ";

            strSQL += @"select EmailSettingId, EmailSettingName, 1 as SortIndex from [EmailSetting] (nolock) order by SortIndex, EmailSettingName";
            return Connection.Query<EmailSetting>(strSQL);
        }

        public IEnumerable<ContactColumn> ContactColumnList()
        {
            return Connection.Query<ContactColumn>(@"select ContactColumnName, ContactColumnFriendlyName from ContactColumn (nolock)");
        }

        public bool IsMyContact(int agentId, int contactId)
        {
            bool result = false;

            Contact cnt = Find(contactId);
            if (cnt != null)
                result = (cnt.AgentId == agentId);

            return result;
        }
    }

    public class ContactCommentRepository : BaseRepository<ContactComment>
    {
        protected override string TableName
        {
            get { return "[ContactComment]"; }
        }

        public IEnumerable<ContactComment> FindByContact(int contactId)
        {
            var result = Connection.Query<ContactComment>(
                @"  SELECT cc.AddDateTime, cc.Comment, cc.ContactId, cc.UserId, 
                            u.FirstName + ' ' + u.LastName as UserName
                    FROM [ContactComment] (nolock) cc left join [User] (nolock) u on cc.UserId = u.Id
                    WHERE cc.ContactId = @ContactId
                    ORDER BY cc.AddDateTime desc", new { ContactId = contactId }).ToList();

            var oldNotes = Connection.Query<ContactComment>(
                @"  SELECT null as UserId, cc.Notes as Comment, cc.ContactId, null as UserId, ' ' as UserName
                    FROM [Contact] (nolock) cc
                    WHERE cc.ContactId = @ContactId and Notes <> ''", new { ContactId = contactId }).ToList();

            result.AddRange(oldNotes);
            return result;
        }

        public override void Insert(ContactComment contactComment)
        {
            Connection.Execute(
                @"INSERT INTO [ContactComment] 
                (ContactId, UserId, Comment, AddDateTime) 
                VALUES (@ContactId, @UserId, @Comment, @AddDateTime)", contactComment);
        }
    }
}
